
***************************************************************************
* Are Consumers' Spending Decisions in Line With an Euler Equation?
* Lena Dräger and Giang Nghiem
* February 2020
**************************************************************************
* This do-file prepares the data for the analysis from the original German Panel on Household Finances Survey//
* conducted by the Bundesbank (the PHF survey).
* Researchers can request the PHF data at the Research Data and Service Centre of the Deutsche Bundesbank //
* https://www.bundesbank.de/en/bundesbank/research/panel-on-household-finances

**************************************************************************
clear

* Data preparation

***********************************************************************	

*First Wave

*Start with the m-file
use "PHF_wave1_v2_0\PHF_m_wave1_v2_0.dta", clear

*Then merge the p-file via hhid, pid, and impid
merge 1:1 hhid pid impid using "PHF_wave1_v2_0\PHF_p_wave1_v2_0.dta" 
drop _merge

*Then merge the h-file via hhid and impid
merge m:1 hhid impid using "PHF_wave1_v2_0\PHF_h_wave1_v2_0.dta" 
drop _merge

*Finally merge the d-file via hhid and impid
merge m:1 hhid impid using "PHF_wave1_v2_0\PHF_d_wave1_v2_0.dta" 
drop _merge

*Select only reference person 
keep if pid == 1 

save "PHF_orig_W1", replace


*Second Wave

*Start with the m-file
use "PHF_wave2_v2_0\PHF_m_wave2_v2_0.dta", clear

*Then merge the p-file via persid and impid
merge 1:1 persid impid using "PHF_wave2_v2_0\PHF_p_wave2_v2_0.dta" 
drop _merge

*Then merge the h-file via hhid and impid
merge m:1 hhid impid using "PHF_wave2_v2_0\PHF_h_wave2_v2_0.dta" 
drop _merge

*Finally merge the d-file via hhid and impid
merge m:1 hhid impid using "PHF_wave2_v2_0\PHF_d_wave2_v2_0.dta" 
drop _merge

*Select only reference person 
keep if pid == 1 

*Save data
save "PHF_orig_W2", replace

append using "PHF_orig_W1.dta"

save "PHF_orig_W1_W2.dta", replace

*******************************************************
****Generate variables of interest
*******************************************************

use "PHF_orig_W1_W2.dta", clear

keep if impid==1 

* Current Consumption

gen cons_past=. //relative consumption past 12 months with a normal year

replace cons_past=1 if dhi0550 == 2  // lower
replace cons_past=2 if dhi0550 == -3 // same
replace cons_past=3 if dhi0550 == 1  // higher


*Saving expectations

gen save_exp = .

replace save_exp = 3 if hni0700==1
replace save_exp = 1 if hni0700==2
replace save_exp = 2 if hni0700==3 

*Qualitative Inflation expectations

gen inf_qual =. 

replace inf_qual = 5 if dhni0800==1
replace inf_qual = 4 if dhni0800==2
replace inf_qual = 3 if dhni0800==3
replace inf_qual = 2 if dhni0800==4
replace inf_qual = 1 if dhni0800==5


*Quantitative inflation expectation (only in wave 2)

gen inf_quan = dhni0850

replace inf_quan = . if dhni0850 < 0
replace inf_quan = . if dhni0800 < 0
replace inf_quan = 0 if dhni0800 == 3
replace inf_quan = (-1)*dhni0850 if inf_fall==1
replace inf_quan = . if abs(inf_quan) > 15 

*Interest rate expectation (0nly in wave 2)

gen rate_qual =. 

replace rate_qual = 5 if dhni1000==1
replace rate_qual = 4 if dhni1000==2
replace rate_qual = 3 if dhni1000==3
replace rate_qual = 2 if dhni1000==4
replace rate_qual = 1 if dhni1000==5 


*Quantitative interest rate expectation

gen rate_quan = .

replace rate_quan = dhni1050 if dhni1050 >=0
replace rate_quan = . if dhni1050 > 5 


*Real income expectations

gen inc_exp = .
replace inc_exp = 3 if hg0800==1 & wave==1
replace inc_exp = 1 if hg0800==2 & wave==1
replace inc_exp = 2 if hg0800==3 & wave==1 

replace inc_exp = 3 if hg0800==1 & wave==2
replace inc_exp = 2 if hg0800==2 & wave==2 
replace inc_exp = 1 if hg0800==3 & wave==2 

* Tax expectations (both wave1 & 2)

gen tax_qual =. 

replace tax_qual = 5 if dhni0100 ==1 
replace tax_qual = 4 if dhni0100 ==2 
replace tax_qual = 3 if dhni0100 ==3 
replace tax_qual = 2 if dhni0100 ==4 
replace tax_qual = 1 if dhni0100 ==5 
****************************************
***DEMOGRAPHIC FACTORS
****************************************

male age age2 linc_refer edu_mh edu_h full_time part_time mini_job retire risk_taker

*Income of reference person
gen inc_refer = dpg0200 + dpg0210 + dpg0400 + dpg0600 + dpg0800 + dpg1000 + dhg0200 + dhg0400 + dhg0600 + dhg0800 + dhg1000 + dhg1150 if pid==1

gen linc_refer =.
replace linc_refer = ln(inc_refer) if inc_refer >0
replace linc_refer = 0 if inc_refer <0

egen inc_refer_25 = pctile(inc_refer) if wave ==2, p(25)
egen inc_refer_50 = pctile(inc_refer) if wave ==2, p(50)
egen inc_refer_75 = pctile(inc_refer) if wave ==2, p(75)

gen inc_refer_q1 = inc_refer <= inc_refer_25
gen inc_refer_q2 = (inc_refer >inc_refer_25 & inc_refer<= inc_refer_50)
gen inc_refer_q3 = (inc_refer >inc_refer_50 & inc_refer<= inc_refer_75)
gen inc_refer_q4 = inc_refer > inc_refer_75 & inc_refer !=.

* Employment

gen une 		= dpe0100a==5 
gen retire 		= dpe0100a==7
gen self_emp 	= pe0800==1
gen full_time 	= dpe0100a==1
gen part_time 	= dpe0100a==2
gen mini_job 	= dpe0100a==3

*Age

gen age 	= ra0300 
gen age2 	= age*age/100

*Age groups

gen age_y   = (age <= 25)
gen age_ml 	= (age > 25 & age<=45)
gen age_mh 	= (45 < age & age <= 65)
gen age_o 	= (age > 65) 

*Levels of Education 
gen edu_h=0
replace edu_h=1 if dpa0400 >=5 & dpa0400 <=7

gen edu_mh=0
replace edu_mh=1 if (dpa0400>=2  &  dpa0400<= 4) | dpa0400==8

gen edu_l=0
replace edu_l=1 if dpa0400==1 | dpa0400==9


*Gender

gen male 	= dpe9040==1
gen female 	= dpe9040==2

*Risk preferences

gen risk_taker = .
replace risk_taker = zi103 if zi103 >=0

save "PHF_Analysis_W1_W2.dta", replace
